﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlServerCe;


namespace myFinance.DAL
{
    class SqlCeDAL : DAL.IDAL
    {
        protected SqlCeConnection Connection;
        protected SqlCeCommand Command;

        public SqlCeDAL()
        {
            this.Connection = new SqlCeConnection();
            this.Connection.ConnectionString = "Data Source=.\\myFinance.sdf;Password=ysf123;";
            this.Connection.Open();

            this.Command = new SqlCeCommand();
            this.Command.Connection = this.Connection;

            random = new Random();
        }

        public int SaveExpence(Entity.IExpence expence)
        {
            var idR = new Random();

            var id = idR.Next(1, 10000);
            this.Command.CommandText =
                "insert into expences (expence_id,description,location_id,category_id,price,date) " +
                "values (" + id + ",'" + expence.Description + "'," + expence.LocationID + "," + 
                expence.CategoryID + "," + expence.Amount + ",'" + expence.Date.ToString("yyyy-M-d hh:mm:ss") + "')";
            this.Command.ExecuteNonQuery();
            return id;

        }

        public int SaveIncome(Entity.IIncome income)
        {
            var id = getNewId();
            this.Command.CommandText = "insert into Income (income_id,name,date) " +
                "values (" + id + ", '" + income.Name + "', '" + income.Date + "' )";
            this.Command.ExecuteNonQuery();
            return id;
        }

        public ICollection<Entity.IExpence> GetAllExpences()
        {
            var expences = new List<myFinance.Entity.IExpence>();

            this.Command.CommandText =
                "select * " +
                "from expences join category on expences.category_id = category.category_id " +
                "join location on expences.location_id = location.location_id order by expences.date desc";
            var reader = this.Command.ExecuteReader();

            while (reader.Read())
            {
                var expence = CreateExpence(reader);
                expences.Add(expence);
            }

            return expences;
        }

        public ICollection<KeyValuePair<int, string>> GetKnownExpenceIdAndNames()
        {
            var kexpenceIdNames = new List<KeyValuePair<int, string>>();
            this.Command.CommandText =
                "select kexpence_id, name from known_expence";
            var reader = this.Command.ExecuteReader();

            while (reader.Read())
            {
                var expenceIdName = new KeyValuePair<int, string>(
                    Convert.ToInt32(reader["kexpence_id"]), Convert.ToString(reader["name"]));
                kexpenceIdNames.Add(expenceIdName);
            }

            return kexpenceIdNames;
        }

        public Entity.IExpence GetKnownExpence(string expenceId)
        {
            var kExpence = new Entity.Expence();
            this.Command.CommandText =
                "select * from known_expence where kexpence_id = " + expenceId;
            var reader = this.Command.ExecuteReader();

            if (reader.Read())
            {
                kExpence = CreateExpence(reader);
            }
            return kExpence;
        }

        public double GetTotalExpence()
        {
            this.Command.CommandText =
                "select sum(expences.price) from expences";
            return Convert.ToDouble(this.Command.ExecuteScalar());
        }

        public double GetRemaining()
        {
            this.Command.CommandText =
                "select sum(income.amount) - sum(expences.price) from income, expences";
            return Convert.ToDouble(this.Command.ExecuteScalar());
        }        

        public void Dispose()
        {
            this.Connection.Close();
        }

        protected static Random random;
        protected static int getNewId()
        {
            return random.Next();
        }

        protected static Entity.Expence CreateExpence(SqlCeDataReader reader)
        {
            var expence = new Entity.Expence()
            {
                ID = Convert.ToInt32(reader["expence_id"]),
                Amount = Convert.ToDouble(reader["price"]),
                CategoryID = Convert.ToInt32(reader["category_id"]),
                Description = Convert.ToString(reader["description"]),
                LocationID = Convert.ToInt32(reader["location_id"]),
            };

            if (reader["date"] != DBNull.Value)
                expence.Date = Convert.ToDateTime(reader["date"]);

            if (reader["name"] != DBNull.Value)
                expence.Location = new Entity.Location()
                                    {
                                        ID = Convert.ToInt32(reader["location_id"]),
                                        Name = Convert.ToString(reader["name"])
                                    };
            if (reader["category_name"] != DBNull.Value)
                expence.Category = new Entity.Category()
                                    {
                                        ID = Convert.ToInt32(reader["category_id"]),
                                        Name = Convert.ToString(reader["category_name"])
                                    };
            return expence;
        }

        ICollection<KeyValuePair<int, string>> IDAL.GetAllCategoriesIdAndNames()
        {
            var categoryIdNames = new List<KeyValuePair<int, string>>();
            this.Command.CommandText =
                "select category_id, category_name from category";
            var reader = this.Command.ExecuteReader();

            while (reader.Read())
            {
                var categoryeIdName = new KeyValuePair<int, string>(
                    Convert.ToInt32(reader["category_id"]), Convert.ToString(reader["category_name"]));
                categoryIdNames.Add(categoryeIdName);
            }

            return categoryIdNames;
        }

        ICollection<KeyValuePair<int, string>> IDAL.GetAllLocationsIdAndNames()
        {
            var locationIdNames = new List<KeyValuePair<int, string>>();
            this.Command.CommandText =
                "select location_id, name from location";
            var reader = this.Command.ExecuteReader();

            while (reader.Read())
            {
                var locationeIdName = new KeyValuePair<int, string>(
                    Convert.ToInt32(reader["location_id"]), Convert.ToString(reader["name"]));
                locationIdNames.Add(locationeIdName);
            }

            return locationIdNames;
        }
        
    }
}
